Imports MyClasses
Imports System.Data
Imports System.Data.SqlClient


Public Class DMiscCode

    Public Shared Function GetMasterCodes() As DataSet
        Dim strSql As String = ""
        Try
            strSql = "SELECT CodeID,CodeDesc FROM mmisccd WHERE Codetype='Basic' AND Status='A'"
            Return DbHelper.ExecuteDataSet(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "GetMasterCodes", strSql)
            Return Nothing
        End Try
    End Function
    ' 01/10/2008
    Public Shared Function GetCodeIdDesc(ByVal codeDesc As String) As DataTable
        Dim strSql As String = ""
        Dim dbcon As New dbAccess
        Try
            ''strSql = "SELECT b.codeid, b.codedesc FROM mmisccd a,mmisccd b " & _
            ''         "WHERE b.status='A' and a.codeid=b.codetype and a.codedesc='" & codeDesc & "'"
            strSql = "SELECT '' as codeid, '' as codedesc FROM mmisccd UNION " & _
                     "SELECT b.codeid, b.codedesc FROM mmisccd a,mmisccd b " & _
                     "WHERE b.status='A' and a.codeid=b.codetype and a.codedesc='" & codeDesc & "'"
            Return dbcon.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "GetCodeIdDesc", strSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function Save(ByVal MiscCode As CMiscCd) As Boolean
        Dim strSql As String = ""
        Dim param(7) As SqlParameter
        Try
            strSql = "INSERT INTO mmisccd(MiscId,CodeType,CodeID,CodeDesc,Status,upd_yn,LU,LD)" & _
                     " VALUES (@MiscId,@CodeType,@CodeID,@CodeDesc,@Status,@upd_yn,@LU,@LD)"

            MiscCode.MiscID = DGeneral.GetSeqNum("MiscCd")
            param(0) = New SqlParameter("@MiscId", MiscCode.MiscID)
            param(1) = New SqlParameter("@CodeType", MiscCode.Code_Type)
            param(2) = New SqlParameter("@CodeID", MiscCode.Code_ID)
            param(3) = New SqlParameter("@CodeDesc", MiscCode.Description)
            param(4) = New SqlParameter("@Status", "A")
            param(5) = New SqlParameter("@upd_yn", "Y")
            param(6) = New SqlParameter("@LU", CCommon.GetUserID())
            param(7) = New SqlParameter("@LD", Date.Now())
            Return DbHelper.ExecuteNonQuery(strSql, param)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "Save", strSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetCodeDesc(ByVal CodeID As String) As String
        Dim strSql As String = ""
        Try

            strSql = "SELECT CodeDesc FROM mmisccd WHERE CodeID='" & CodeID & "'"
            Return DbHelper.ExecuteScalar(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "Save", strSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetCodeType(ByVal CodeID As String) As String
        Dim strSql As String = ""
        Try
            strSql = "SELECT Codetype FROM mmisccd WHERE CodeID='" & CodeID & "'"
            Return DbHelper.ExecuteScalar(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "Save", strSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetCodes(ByVal CodeType As String, ByVal OrderByIDorDesc As Char) As DataTable

        Dim strSql As String = ""
        Try
            strSql = "SELECT CodeID,CodeDesc FROM mmisccd WHERE Status='A' AND Codetype='" & CodeType & "' ORDER BY " & IIf(OrderByIDorDesc = "D", "CodeDesc", "CodeID")
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "GetCodes", strSql)
            Return Nothing
        End Try
    End Function
    '    24/11/2007
    Public Shared Function GetMiscCodes(ByVal MiscId As Integer) As DataSet
        Dim strSql As String = ""
        Try
            strSql = "SELECT a.MiscId, b.CodeDesc, a.CodeID, a.CodeDesc, a.upd_yn FROM mmisccd a, mmisccd b WHERE a.Status='A' AND b.CodeId = a.CodeType AND a.MiscId='" & MiscId & "' ORDER BY a.CodeType"
            Return DbHelper.ExecuteDataSet(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "GetMiscCodes", strSql)
            Return Nothing
        End Try
    End Function

    '    21/04/2008
    Public Shared Function GetLastCd(ByVal CodeType As String) As String
        Dim strCheck As String
        Dim strSql As String = ""
        Try
            strCheck = "SELECT ISNUMERIC(max(CodeID)) AS LastCodeID FROM mMiscCD WHERE CodeType='" & CodeType & "'"
            If DbHelper.ExecuteScalar(strCheck) = "0" Then
                Return ""
                Exit Function
            Else
                strSql = "SELECT max(Convert(int,CodeID)) FROM mMiscCD WHERE CodeType='" & CodeType & "'"
            End If
            'strSql = "SELECT MAX(CodeID) FROM mMiscCD WHERE CodeType='" & CodeType & "'"
            Return DbHelper.ExecuteScalar(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "GetLastCd", strSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetMaxValue(ByVal CodeType As String) As Integer
        Try
            Dim strSql As String = "SELECT MAX(CAST(CodeID AS INTEGER)) FROM mMiscCD WHERE CodeType='" & CodeType & "'"
            If IsDBNull(DbHelper.ExecuteScalar(strSql)) Then : Return 0
            Else : Return DbHelper.ExecuteScalar(strSql) + 1
            End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "GetMaxValue")
        End Try
    End Function

    Public Shared Function GetCodesWithBlank(ByVal CodeType As String, ByVal OrderByIDorDesc As Char) As DataTable
        Try
            Return DbHelper.ExecuteDataTable("SELECT '0' as CodeID,'' as CodeDesc FROM mmisccd UNION SELECT CodeID,CodeDesc FROM mmisccd WHERE Status='A' AND Codetype='" & CodeType & "' ORDER BY " & IIf(OrderByIDorDesc = "D", "CodeDesc", "CodeID"))
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "GetCodesWithBlank")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetCodes() As DataTable
        Try
            Return DbHelper.ExecuteDataTable("SELECT Codetype,CodeID,CodeDesc FROM mmisccd WHERE Status='A' AND Codetype <> 'Basic'")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "GetCodes")
            Return Nothing
        End Try
    End Function

    Public Shared Function CodeIDExist(ByVal CodeID As String) As Boolean
        Dim strSql As String = ""
        Try
            strSql = "SELECT COUNT(CodeDesc) FROM mmisccd WHERE CodeID='" & CodeID & "'"
            If DbHelper.ExecuteScalar(strSql) > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "Update", strSql)
        End Try
    End Function

    Public Shared Function Update(ByVal MiscCode As CMiscCd) As Boolean
        Dim strSql As String = ""
        Dim param(4) As SqlParameter
        Try
            strSql = "UPDATE mmisccd SET CodeDesc=@CodeDesc,LU=@LU,LD=@LD WHERE CodeType=@CodeType AND CodeID=@CodeID"
            param(0) = New SqlParameter("@CodeDesc", MiscCode.Description)
            param(1) = New SqlParameter("@LU", CCommon.GetUserID())
            param(2) = New SqlParameter("@LD", Date.Now())
            param(3) = New SqlParameter("@CodeType", MiscCode.Code_Type)
            param(4) = New SqlParameter("@CodeID", MiscCode.Code_ID)

            Return DbHelper.ExecuteNonQuery(strSql, param)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "Update", strSql)
            Return False
        End Try

    End Function

    Public Shared Function GetTitles() As DataSet
        Try
            Return DbHelper.ExecuteDataSet("SELECT CodeID,CodeDesc FROM mmisccd WHERE codetype='TITLE'")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "GetTitles")
            Return Nothing
        End Try
    End Function
    '******************************************************************
    ' 07/02/2008 : Code Regarding Delete Operation Of Master codes
    '<<Start
    Public Shared Function GetFromMpItem(ByVal CodeType As String) As DataTable
        Try
            Select Case CodeType
                Case "SCHEDULETYPE"
                    Return DbHelper.ExecuteDataTable("SELECT ScheduleDrug FROM mpitem WHERE Status='A'")
                Case "DRUGACTION"
                    Return DbHelper.ExecuteDataTable("SELECT Action1,Action2,Action3 FROM mpitem WHERE Status='A'")
                Case "GENERICNAME"
                    Return DbHelper.ExecuteDataTable("SELECT GenericName FROM mpitem WHERE Status='A'")
                Case "ITEMGRADE"
                    Return DbHelper.ExecuteDataTable("SELECT Grade FROM mpitem WHERE Status='A'")
                Case "ITEMFORM"
                    Return DbHelper.ExecuteDataTable("SELECT ItemForm FROM mpitem WHERE Status='A'")
                Case "ITEM_CATEGORY"
                    Return DbHelper.ExecuteDataTable("SELECT ItemCatID FROM mpitem WHERE Status='A'")
                Case "ITEM_TYPE"
                    Return DbHelper.ExecuteDataTable("SELECT ItemType FROM mpitem WHERE Status='A'")
                Case Else
                    Return Nothing
            End Select
            'Return DbHelper.ExecuteDataTable("SELECT ItemID,Action1,Action2,Action3,ScheduleDrug FROM mpitem WHERE Status='A'")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "GetFromMpItem")
            Return Nothing
        End Try
    End Function
    Public Shared Function GetCardType() As DataTable
        Try
            Return DbHelper.ExecuteDataTable("SELECT CardType FROM mCarddet WHERE Status='A'")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "GetCardType")
            Return Nothing
        End Try
    End Function
    Public Shared Function GetDoctrSpeciality(ByVal CodeType As String) As DataTable
        Try
            Select Case CodeType
                Case "DOCTOR_SPECIALITY"
                    Return DbHelper.ExecuteDataTable("SELECT Specialisation FROM mpDoctor WHERE Status='A'")
                Case "QUALIFICATION"
                    Return DbHelper.ExecuteDataTable("SELECT Qualification FROM mpDoctor WHERE Status='A'")
                Case Else
                    Return Nothing
            End Select
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "GetDoctrSpeciality")
            Return Nothing
        End Try
    End Function
    Public Shared Function GetEmpQual() As DataTable
        Try
            Return DbHelper.ExecuteDataTable("SELECT EduQual FROM mEmp WHERE Status='A'")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "GetEmpQual")
            Return Nothing
        End Try
    End Function
    Public Shared Function GetAddressType() As DataTable
        Try
            Return DbHelper.ExecuteDataTable("SELECT addType FROM mAddress WHERE Status='A'")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "GetAddressType")
            Return Nothing
        End Try
    End Function
    Public Shared Function GetLocType() As DataTable
        Try
            Return DbHelper.ExecuteDataTable("SELECT LocationType FROM mLocation")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "GetLocType")
            Return Nothing
        End Try
    End Function
    Public Shared Function GetPurSale(ByVal CodeType As String) As DataTable
        Try
            Select Case CodeType
                Case "PURPACK"
                    Return DbHelper.ExecuteDataTable("select PurPack, SellPack from tstock WHERE Status='A'")
                Case "SALEUNIT"
                    Return DbHelper.ExecuteDataTable("select PurUnit, SellUnit from tstock WHERE Status='A'")
                Case Else
                    Return Nothing
            End Select
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DMiscCode", "GetPurSale")
            Return Nothing
        End Try
    End Function
    Public Shared Function DeleteMiscCode(ByVal CodeID As String, ByVal CodeType As String) As String
        Try
            Return DbHelper.ExecuteNonQuery("DELETE FROM mmiscCd WHERE CodeID = '" & CodeID & "' AND CodeType = '" & CodeType & "'")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DVendor", "DeleteMiscCode")
            Return Nothing
        End Try
    End Function
    '>>End  07/02/2008
End Class
